<?php
class ModelCommunityUcp extends Model{
    /**************overview start**************/
    public function getUserRoleTitleAndRankTitle($role_id,$rank_id){
        $query=$this->db->query("SELECT rt.role_title, rat.rank_title FROM " . DB_PREFIX . "community_role_title AS rt LEFT JOIN " . DB_PREFIX . "community_rank_title AS rat ON (rat.rank_id='" . (int)$rank_id . "' AND rat.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE rt.role_id='" . (int)$role_id . "' AND rt.language_id='" . (int)$this->config->get('config_language_id') . "' LIMIT 0,1");

        return $query->row;
    }

    public function getDrafts(){
        $query=$this->db->query("SELECT d.*, fn.forum_name, f.forum_id, t.topic_title, t.topic_id, t.forum_id AS topic_forum_id FROM " . DB_PREFIX . "community_drafts AS d LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (d.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (d.topic_id=t.topic_id) WHERE d.user_id='" . (int)$this->customer->getForumUserId() . "' ORDER BY d.save_time DESC");

        return $query->rows;
    }

    public function deleteDrafts($idStr){
        $this->db->query("DELETE FROM " . DB_PREFIX . "community_drafts WHERE draft_id IN ($idStr)");
    }

    //bookmark
    public function getBookmarks($start,$limit){
        $query=$this->db->query("SELECT b.*, tt.mark_time, t.forum_id, t.topic_id, t.topic_title, t.topic_time, t.topic_first_poster_name, t.topic_last_poster_name, t.topic_last_post_time, t.topic_type, t.topic_status FROM " . DB_PREFIX . "community_bookmarks AS b LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (b.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_topics_track AS tt ON (b.topic_id=tt.topic_id AND tt.user_id='" . (int)$this->customer->getForumUserId() . "') WHERE b.user_id='" . (int)$this->customer->getForumUserId() . "' AND b.topic_deleted='0' ORDER BY t.topic_last_post_time DESC LIMIT " . $start . "," . $limit);

        return $query->rows;
    }

    public function getBookmarksTotal(){
        $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_bookmarks WHERE user_id='" . (int)$this->customer->getForumUserId() . "' AND topic_deleted='0'");

        return $query->row['total'];
    }

    public function deleteBookmarks($idStr){
        $this->db->query("DELETE FROM " . DB_PREFIX . "community_bookmarks WHERE topic_id IN ($idStr) AND user_id='" . (int)$this->customer->getForumUserId() . "'");
    }

    //topic subscription
    public function getTopicSubscription($start,$limit){
        $query=$this->db->query("SELECT tn.topic_id, t.forum_id, t.topic_title, t.topic_time, t.topic_first_poster_name, t.topic_last_poster_name,t.topic_last_post_time, t.topic_type, t.topic_status, tt.mark_time FROM " . DB_PREFIX . "community_topic_notify AS tn LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (tn.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_topics_track AS tt ON (tn.topic_id=tt.topic_id AND tt.user_id='" . (int)$this->customer->getForumUserId() . "') WHERE tn.user_id='" . (int)$this->customer->getForumUserId() . "' AND tn.topic_deleted='0' ORDER BY t.topic_last_post_time DESC LIMIT " . $start . "," . $limit);

        return $query->rows;
    }

    public function getTopicSubscriptionTotal(){
        $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_topic_notify WHERE user_id='" . (int)$this->customer->getForumUserId() . "' AND topic_deleted='0'");

        return $query->row['total'];
    }

    //forum subscription
    public function getForumSubscription(){
        $query=$this->db->query("SELECT fnd.forum_name, fnd.forum_desc, f.forum_id, f.forum_posts, f.forum_last_poster_name, f.forum_last_post_time, ft.mark_time FROM " . DB_PREFIX . "community_forum_notify AS fn LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=fn.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fnd ON (fnd.forum_id=f.forum_id AND fnd.language_id='" . (int)$this->config->get('config_language_id') . "') LEFT JOIN " . DB_PREFIX . "community_forum_track AS ft ON (ft.forum_id=fn.forum_id AND ft.user_id='" . (int)$this->customer->getForumUserId() . "') WHERE fn.user_id='" . (int)$this->customer->getForumUserId() . "' ORDER BY fnd.forum_name ASC");

        return $query->rows;
    }

    public function deleteSubscriptions($topicIdStr,$forumIdStr){
        if(isset($topicIdStr)){
            $this->db->query("DELETE FROM " . DB_PREFIX . "community_topic_notify WHERE topic_id IN ($topicIdStr) AND user_id='" . (int)$this->customer->getForumUserId() . "'");
        }

        if(isset($forumIdStr)){
            $this->db->query("DELETE FROM " . DB_PREFIX . "community_forum_notify WHERE forum_id IN ($forumIdStr) AND user_id='" . (int)$this->customer->getForumUserId() . "'");
        }
    }
    /**************overview end**************/

    /**************profile start**************/
    public function updateProfile($postData){
        if($postData['birthday_month']=='--' && $postData['birthday_day']=='--' && $postData['birthday_year']=='--'){
            $birthday='';
        }else{
            $birthday=$this->db->escape($postData['birthday_month']) . '-' . $this->db->escape($postData['birthday_day']) . '-' . $this->db->escape($postData['birthday_year']);
        }

        $this->db->query("UPDATE " . DB_PREFIX . "community_users SET user_from='" . $this->db->escape($postData['location']) . "', user_icq='" . $this->db->escape($postData['icq']) . "', user_aim='" . $this->db->escape($postData['aol']) . "', user_yim='" . $this->db->escape($postData['yahoo']) . "', user_msnm='" . $this->db->escape($postData['wl_msn']) . "', user_jabber='" . $this->db->escape($postData['jabber']) . "', user_occ='" . $this->db->escape($postData['occupation']) . "', user_interests='" . $this->db->escape($postData['interests']) . "', user_birthday='" . $birthday . "' WHERE user_id='" . (int)$this->customer->getForumUserId() . "'");
    }

    public function updateSignature($postData){
        $this->db->query("UPDATE " . DB_PREFIX . "community_users SET user_sig='" . $this->db->escape($postData['content']) . "' WHERE user_id='" . (int)$this->customer->getForumUserId() . "'");
    }

    public function updateAvatar($avatarPath){
        $this->db->query("UPDATE " . DB_PREFIX . "community_users SET user_avatar='" . $this->db->escape($avatarPath) . "' WHERE user_id='" . (int)$this->customer->getForumUserId() . "'");
    }

    public function updateForumUserName($postData){
        $query=$this->db->query("SELECT email, date_added FROM " . DB_PREFIX . "customer WHERE customer_id='" . $this->customer->getId() . "' LIMIT 0,1");

        $this->db->query("INSERT INTO " . DB_PREFIX . "community_users SET username='" . $this->db->escape($postData['forumusername']) . "', customer_id='" . (int)$this->customer->getId() . "', user_type='5' , user_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', user_regdate='" . strtotime($query->row['date_added']) . "', user_email='" . $this->db->escape($query->row['email']) . "', user_actkey='" . $this->db->escape(md5(time() . mt_rand())) . "', user_lastvisit='" . time() . "'");

        $uid=$this->db->getLastId();

        $this->db->query("UPDATE " . DB_PREFIX . "setting SET `value`=`value`+1 WHERE `key`='total_users'");
        $this->db->query("UPDATE " . DB_PREFIX . "setting SET `value`='" . $this->db->escape(serialize(array($uid,$this->db->escape($postData['forumusername'])))) . "' WHERE `key`='newest_member'");
    }
    /**************profile end**************/

    /**************board preference start**************/
    public function updateGlobalSettings($postData){
        $view_online= $postData['hide_online_status'] ? 0 : 1;

        $this->db->query("UPDATE " . DB_PREFIX . "community_users SET user_allow_email='" . (int)$postData['allow_user_email'] . "', user_allow_pm='" . (int)$postData['allow_user_pm'] . "', user_allow_viewonline='" . (int)$view_online . "', user_notify_pm='" . (int)$postData['pm_notify'] . "' WHERE user_id='" . (int)$this->customer->getForumUserId() . "'");
    }

    public function updatePostingDefault($postData){
        $this->db->query("UPDATE " . DB_PREFIX . "community_users SET user_attach_bbcode='" . (int)$postData['enable_bbcode'] . "', user_attach_smiles='" . (int)$postData['enable_smiles'] . "', user_attach_sig='" . (int)$postData['attach_signature'] . "', user_attach_notify='" . (int)$postData['reply_notify'] . "' WHERE user_id='" . (int)$this->customer->getForumUserId() . "'");
    }
    /**************board preference end**************/

    /**************private message start**************/
    public function checkUser($postData){
        $query=$this->db->query("SELECT user_id, user_allow_pm FROM " . DB_PREFIX . "community_users WHERE username='" . $postData['add_member'] . "'");

        return $query->row;
    }

    public function getUserAndPmInfo($getData){
        $query=$this->db->query("SELECT p.message_subject, u.username, u.user_allow_pm FROM " . DB_PREFIX . "community_privmsgs AS p LEFT JOIN " . DB_PREFIX . "community_users AS u ON (p.author_id=u.user_id) WHERE p.msg_id='" . (int)$getData['mid'] . "'");

        return $query->row;
    }

    public function getUserInfo($getData){
        $query=$this->db->query("SELECT username, user_allow_pm FROM " . DB_PREFIX . "community_users WHERE user_id='" . (int)$getData['uid'] . "'");

        return $query->row;
    }

    public function addPrivateMessage($to_member_id_list,$postData,$getData){
        if(isset($getData['mid']) && isset($getData['hid']) && isset($getData['uid'])){
            if($getData['hid']==0){
                $history_id=$getData['mid'];
            }else{
                $history_id=$getData['hid'];
            }
        }else{
            $history_id=0;
        }

        $this->db->query("INSERT INTO " . DB_PREFIX . "community_privmsgs SET msg_history_id='" . (int)$history_id . "', author_id='" . (int)$this->customer->getForumUserId() . "', message_time='" . (int)time() . "', message_subject='" . $this->db->escape($postData['subject']) . "', message_text='" . $this->db->escape($postData['content']) . "', to_address='" . $this->db->escape(serialize($to_member_id_list)) . "'");

        $msg_id=$this->db->getLastId();

        $values=array();
        $member_id_str='';

        foreach($to_member_id_list as $member_id){
            $values[]="('" . (int)$msg_id . "', '" . (int)$this->customer->getForumUserId() . "', '" . (int)$member_id . "', '1', '0')";
            $member_id_str.=$member_id . ', ';
        }

        $values[]="('" . (int)$msg_id . "', '" . (int)$this->customer->getForumUserId() . "', '" . (int)$this->customer->getForumUserId() . "', '0', '0')";

        $valueStr=implode(', ',$values);
        $valueStr=rtrim($valueStr,', ');

        $member_id_str=rtrim($member_id_str,', ');

        $this->db->query("INSERT INTO " . DB_PREFIX . "community_privmsgs_to (msg_id, author_id, user_id, pm_unread, pm_replied) VALUES $valueStr");

        if(isset($getData['mid']) && isset($getData['hid']) && isset($getData['uid'])){
            $this->db->query("UPDATE " . DB_PREFIX . "community_privmsgs_to SET pm_replied='1' WHERE msg_id='" . (int)$getData['mid'] . "' AND (user_id>author_id OR user_id<author_id)");
        }

        $this->db->query("UPDATE " . DB_PREFIX . "community_users SET user_new_privmsg=user_new_privmsg+1 WHERE user_id IN ($member_id_str)");
    }

    public function getPrivateMessageTotal(){
        $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_privmsgs_to WHERE user_id='" . (int)$this->customer->getForumUserId() . "' AND (author_id>'" . (int)$this->customer->getForumUserId() . "' OR author_id<'" . (int)$this->customer->getForumUserId() . "')");

        return $query->row['total'];
    }

    public function loadPrivateMessages($start,$limit){
        $query=$this->db->query("SELECT pt.pm_unread, pt.pm_replied, p.*, u.username, r.user_id AS reporter_id FROM " . DB_PREFIX . "community_privmsgs_to AS pt LEFT JOIN " . DB_PREFIX . "community_privmsgs AS p ON (pt.msg_id=p.msg_id) LEFT JOIN " . DB_PREFIX . "community_users AS u ON (pt.author_id=u.user_id) LEFT JOIN " . DB_PREFIX . "community_reports AS r ON (r.pm_id=pt.msg_id AND r.report_closed='0') WHERE pt.user_id='" . (int)$this->customer->getForumUserId() . "' AND (pt.user_id>pt.author_id OR pt.user_id<pt.author_id) ORDER BY p.message_time DESC LIMIT " . $start . "," . $limit);

        return $query->rows;
    }

    public function getSentMessageTotal(){
        $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_privmsgs_to WHERE author_id='" . (int)$this->customer->getForumUserId() . "' AND user_id=author_id");

        return $query->row['total'];
    }

    public function loadSentMessages($start,$limit){
        $query=$this->db->query("SELECT pt.pm_unread, pt.pm_replied, p.*, r.user_id AS reporter_id FROM " . DB_PREFIX . "community_privmsgs_to AS pt LEFT JOIN " . DB_PREFIX . "community_privmsgs AS p ON (pt.msg_id=p.msg_id) LEFT JOIN " . DB_PREFIX . "community_reports AS r ON (r.pm_id=pt.msg_id AND r.report_closed='0') WHERE pt.author_id='" . (int)$this->customer->getForumUserId() . "' AND pt.user_id=pt.author_id ORDER BY p.message_time DESC LIMIT " . $start . "," . $limit);

        return $query->rows;
    }

    public function deletePrivateMessages($idStr,$idArr){
        $this->db->query("DELETE FROM " . DB_PREFIX . "community_privmsgs_to WHERE msg_id IN (" . $idStr . ") AND user_id=" . (int)$this->customer->getForumUserId() . " AND (user_id>author_id OR user_id<author_id)");

        foreach($idArr as $id){
            $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_privmsgs_to WHERE msg_id='" . (int)$id . "'");
            $total=$query->row['total'];

            if($total==0){
                $this->db->query("DELETE FROM " . DB_PREFIX . "community_privmsgs WHERE msg_id='" . (int)$id . "'");
            }
        }
    }

    public function deleteSentMessages($idStr,$idArr){
        $this->db->query("DELETE FROM " . DB_PREFIX . "community_privmsgs_to WHERE msg_id IN (" . $idStr . ") AND user_id=" . (int)$this->customer->getForumUserId() . " AND user_id=author_id");

        foreach($idArr as $id){
            $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_privmsgs_to WHERE msg_id='" . (int)$id . "'");
            $total=$query->row['total'];

            if($total==0){
                $this->db->query("DELETE FROM " . DB_PREFIX . "community_privmsgs WHERE msg_id='" . (int)$id . "'");
            }
        }
    }

    public function viewPrivateMessage($getData){
        $query=$this->db->query("SELECT p.*, u.username, pt.pm_unread, pt.pm_replied FROM " . DB_PREFIX . "community_privmsgs_to AS pt LEFT JOIN " . DB_PREFIX . "community_privmsgs AS p ON (p.msg_id=pt.msg_id) LEFT JOIN " . DB_PREFIX . "community_users AS u ON (pt.author_id=u.user_id) WHERE pt.msg_id=" . (int)$getData['mid'] . " AND pt.user_id='" . (int)$this->customer->getForumUserId() . "' AND (pt.user_id>pt.author_id OR pt.user_id<pt.author_id)");

        return $query->row;
    }

    public function viewPrivateMessageHistory($getData,$historyId){
        if($historyId){//if not the first pm in pm history
            $query=$this->db->query("SELECT p.*, u.username FROM " . DB_PREFIX . "community_privmsgs AS p LEFT JOIN " . DB_PREFIX . "community_users AS u ON (p.author_id=u.user_id) WHERE p.msg_history_id='" . (int)$historyId . "' OR p.msg_id='" . (int)$historyId . "' ORDER BY p.message_time DESC");
        }else{
            $query=$this->db->query("SELECT p.*, u.username FROM " . DB_PREFIX . "community_privmsgs AS p LEFT JOIN " . DB_PREFIX . "community_users AS u ON (p.author_id=u.user_id) WHERE p.msg_history_id='" . (int)$getData['mid'] . "' OR p.msg_id='" . (int)$getData['mid'] . "' ORDER BY p.message_time DESC");
        }
        return $results=$query->rows;
    }

    public function viewSentMessage($getData){
        $query=$this->db->query("SELECT p.* FROM " . DB_PREFIX . "community_privmsgs_to AS pt LEFT JOIN " . DB_PREFIX . "community_privmsgs AS p ON (pt.msg_id=p.msg_id) WHERE pt.author_id='" . (int)$this->customer->getForumUserId() . "' AND pt.user_id=pt.author_id AND pt.msg_id='" . $getData['mid'] . "'");

        return $query->row;
    }

    public function viewSentMessageHistory($getData,$historyId){
        if($historyId){//if not the first pm in pm history
            $query=$this->db->query("SELECT p.*, u.username FROM " . DB_PREFIX . "community_privmsgs AS p LEFT JOIN " . DB_PREFIX . "community_users AS u ON (p.author_id=u.user_id) WHERE p.msg_history_id='" . (int)$historyId . "' OR p.msg_id='" . (int)$historyId . "' ORDER BY p.message_time DESC");
        }else{
            $query=$this->db->query("SELECT p.*, u.username FROM " . DB_PREFIX . "community_privmsgs AS p LEFT JOIN " . DB_PREFIX . "community_users AS u ON (p.author_id=u.user_id) WHERE p.msg_history_id='" . (int)$getData['mid'] . "' OR p.msg_id='" . (int)$getData['mid'] . "' ORDER BY p.message_time DESC");
        }
        return $results=$query->rows;
    }

    public function updateMessageReadStatus($getData){
        $this->db->query("UPDATE " . DB_PREFIX . "community_privmsgs_to AS pt, " . DB_PREFIX . "community_users AS u SET pt.pm_unread='0', u.user_new_privmsg=u.user_new_privmsg-1 WHERE pt.msg_id='" . (int)$getData['mid'] . "' AND pt.user_id='" . (int)$this->customer->getForumUserId() . "' AND (pt.user_id>pt.author_id OR pt.user_id<pt.author_id) AND u.user_id=pt.user_id");
    }
    /**************private message end**************/

    /**************friend and foe begin**************/
    public function checkFriendUserNamesExist($friendNames){
        $friendNames=nl2br($friendNames);
        $friendNames=explode('<br />',$friendNames);

        foreach($friendNames as $friendName){
            $query=$this->db->query("SELECT user_id FROM " . DB_PREFIX . "community_users WHERE username='" . trim($friendName) . "'");

            if(!empty($query->row)){
                $nameNotExist=TRUE;
                break;
            }
        }

        if($nameNotExist){
            return TRUE;
        }else{
            return FALSE;
        }
    }

    public function checkAlreadyFriend($friendNames){
        $friendNames=nl2br($friendNames);
        $friendNames=explode('<br />',$friendNames);

        foreach($friendNames as $friendName){
            $query=$this->db->query("SELECT z.zebra_id FROM " . DB_PREFIX . "community_zebra AS z LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.username=" . trim($friendName) . ") WHERE z.zebra_id=u.user_id and z.user_id='" . (int)$this->customer->getForumUserId() . "'");

            if(!empty($query->row)){
                $FriendExist=TRUE;
                break;
            }
        }

        if($FriendExist){
            return TRUE;
        }else{
            return FALSE;
        }
    }

    public function addFriend($friendNames){
        $this->language->load('community/ucp');

        $friend_id_list=array();

        foreach($friendNames as $friendName){
            $query=$this->db->query("SELECT user_id FROM " . DB_PREFIX . "community_users WHERE username='" . $this->db->escape($friendName) . "' LIMIT 0,1");

            $this->db->query("INSERT INTO " . DB_PREFIX . "community_zebra SET user_id='" . (int)$this->customer->getForumUserId() . "', zebra_id='" . (int)$query->row['user_id'] . "', req_friends='1'");

            $friend_id_list[]=$query->row['user_id'];
        }

        $this->db->query("INSERT " . DB_PREFIX . "community_privmsgs SET author_id='0', message_time='" . (int)time() . "', message_subject='" . $this->db->escape(sprintf($this->language->get('text_friend_request'),$this->customer->getForumUserName())) . "', message_text='" . $this->db->escape(sprintf($this->language->get('text_friend_request_content'),$this->customer->getForumUserName())) . "', to_address='" . $this->db->escape(serialize($friend_id_list)) . "'");

        $msg_id=$this->db->getLastId();

        $values=array();
        $friend_id_str='';

        foreach($friend_id_list as $friend_id){
            $values[]="('" . (int)$msg_id . "', '0', '" . (int)$friend_id . "', '1', '0')";
            $friend_id_str.=$friend_id . ', ';
        }

        $valueStr=implode(', ',$values);
        $valueStr=rtrim($valueStr,', ');

        $this->db->query("INSERT INTO " . DB_PREFIX . "community_privmsgs_to (msg_id, author_id, user_id, pm_unread, pm_replied) VALUES $valueStr");
    }
    /**************friend and foe end**************/


}
?>